using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Xml;
using Chaotic.DataBase.DataAccess;
using Chaotic.DataBase.DataAccess.Entity;
using Oracle.ManagedDataAccess.Client;

namespace Chaotic.DataBase.OracleAccess;

public class OracleDataAccessClient : AbstractDataAccess
{
	private OracleConnection _mOracleConnection;

	private OracleTransaction? _trans;

	public override DatabaseType DatabaseType => DatabaseType.Oracle;

	public override IDbConnection DbConnection => _mOracleConnection;
 
	public OracleDataAccessClient(string conString)
	{
		_mOracleConnection = new OracleConnection(conString);
		int num = conString.IndexOf("User Id=", StringComparison.Ordinal) + "User Id=".Length;
		int num2 = conString.IndexOf(';', num);
		base.Owner = conString.Substring(num, num2 - num).ToUpper();
	}

	public override void Close()
	{
		base.Close();
		_mOracleConnection = new OracleConnection(_mOracleConnection.ConnectionString);
	}

	public override IDbTransaction? BeginTransaction()
	{
		_trans = _mOracleConnection.BeginTransaction();
		return _trans;
	}

	public override DataSet? ExecuteDataset(CommandType commandType, string? commandText, QueryParameterCollection commandParameters, DataSet? ds, string tableName)
	{
		LogOutPut(delegate
		{
			OracleCommand oracleCommand = new OracleCommand();
			PrepareCommand(oracleCommand, commandType, commandText, commandParameters);
			OracleDataAdapter oracleDataAdapter = new OracleDataAdapter(oracleCommand);
			if (object.Equals(tableName, null) || tableName.Length < 1)
			{
				oracleDataAdapter.Fill(ds);
			}
			else
			{
				oracleDataAdapter.Fill(ds, tableName);
			}
			for (int i = 0; i < oracleCommand.Parameters.Count; i++)
			{
				if (oracleCommand.Parameters[i].Direction == ParameterDirection.Output)
				{
					commandParameters[i].Value = oracleCommand.Parameters[i].Value;
				}
			}
			oracleCommand.Parameters.Clear();
			return (ds != null && ds.Tables.Count > 0) ? ds.Tables[0].Rows.Count : 0;
		}, "ExecuteDataset", commandText, commandParameters);
		return ds;
	}

	public override DataSet? ExecuteDataset(string? commandText, QueryParameterCollection commandParameters, DataSet? ds, int startRecord, int maxRecords, string tableName)
	{
		LogOutPut(delegate
		{
			OracleCommand oracleCommand = new OracleCommand();
			PrepareCommand(oracleCommand, CommandType.Text, commandText, commandParameters);
			OracleDataAdapter oracleDataAdapter = new OracleDataAdapter(oracleCommand);
			if (object.Equals(tableName, null) || tableName.Trim() == string.Empty)
			{
				oracleDataAdapter.Fill(ds);
			}
			else
			{
				oracleDataAdapter.Fill(ds, startRecord, maxRecords, tableName);
			}
			oracleCommand.Parameters.Clear();
			return (ds != null && ds.Tables.Count > 0) ? ds.Tables[0].Rows.Count : 0;
		}, "ExecuteDataset", commandText, commandParameters);
		return ds;
	}

	public override int ExecuteNonQuery(CommandType commandType, string? commandText, QueryParameterCollection commandParameters, DataRow row, SqlStruct sqlStruct)
	{
		int tmpValue = 0;
		LogOutPut(delegate
		{
			OracleCommand oracleCommand = new OracleCommand();
			if (sqlStruct == null)
			{
				PrepareCommand(oracleCommand, commandType, commandText, commandParameters);
			}
			else
			{
				for (int i = 0; i < sqlStruct.ParamFields.Length; i++)
				{
					if (sqlStruct.ParamFields[i].ParamName.IndexOf('_') != 1)
					{
						commandParameters.Add(sqlStruct.ParamFields[i].ParamName, row[sqlStruct.ParamFields[i].FieldName]);
					}
					else if (sqlStruct.ParamFields[i].ParamName.IndexOf('@') == 0)
					{
						commandParameters.Add(sqlStruct.ParamFields[i].ParamName.Replace("@_", ":Origina"), row[sqlStruct.ParamFields[i].FieldName, DataRowVersion.Original]);
					}
					else
					{
						commandParameters.Add(sqlStruct.ParamFields[i].ParamName.Replace(":_", ":Origina"), row[sqlStruct.ParamFields[i].FieldName, DataRowVersion.Original]);
					}
				}
				PrepareCommand(oracleCommand, CommandType.Text, sqlStruct.SqlString, commandParameters);
			}
			tmpValue = oracleCommand.ExecuteNonQuery();
			for (int j = 0; j < oracleCommand.Parameters.Count; j++)
			{
				if (oracleCommand.Parameters[j].Direction == ParameterDirection.Output)
				{
					commandParameters[j].Value = oracleCommand.Parameters[j].Value;
				}
			}
			oracleCommand.Parameters.Clear();
			return tmpValue;
		}, "ExecuteNonQuery", commandText, commandParameters);
		return tmpValue;
	}

	public override IDataReader ExecuteReader(CommandType commandType, string? commandText, QueryParameterCollection commandParameters)
	{
		IDataReader dr = null;
		LogOutPut(delegate
		{
			OracleCommand oracleCommand = new OracleCommand();
			PrepareCommand(oracleCommand, commandType, commandText, commandParameters);
			dr = oracleCommand.ExecuteReader();
			oracleCommand.Parameters.Clear();
			return -1;
		}, "ExecuteReader", commandText, commandParameters);
		return dr;
	}

	public override object? ExecuteScalar(CommandType commandType, string? commandText, QueryParameterCollection commandParameters)
	{
		object? tmpValue = null;
		LogOutPut(delegate
		{
			OracleCommand oracleCommand = new OracleCommand();
			PrepareCommand(oracleCommand, commandType, commandText, commandParameters);
			tmpValue = oracleCommand.ExecuteScalar();
			oracleCommand.Parameters.Clear();
			return -1;
		}, "ExecuteScalar", commandText, commandParameters);
		return tmpValue;
	}

	public override XmlReader ExecuteXmlReader(CommandType commandType, string? commandText, QueryParameterCollection commandParameters)
	{
		XmlReader reader = null;
		LogOutPut(delegate
		{
			OracleCommand oracleCommand = new OracleCommand();
			PrepareCommand(oracleCommand, commandType, commandText, commandParameters);
			reader = oracleCommand.ExecuteXmlReader();
			oracleCommand.Parameters.Clear();
			return -1;
		}, "ExecuteXmlReader", commandText, commandParameters);
		return reader;
	}

	private void PrepareCommand(OracleCommand cmd, CommandType commandType, string? commandText, QueryParameterCollection commandParameters)
	{
		commandText = commandText.Replace("@", ":");
		if (commandText.IndexOf(":_", StringComparison.Ordinal) >= 0)
		{
			commandText = commandText.Replace(":_", ":Origina");
		}
		cmd.CommandType = commandType;
		cmd.CommandText = commandText;
		cmd.Connection = _mOracleConnection;
		cmd.Transaction = _trans;
		cmd.CommandTimeout = 600;
		if (commandParameters != null && commandParameters.Count > 0)
		{
			for (int i = 0; i < commandParameters.Count; i++)
			{
				cmd.Parameters.Add(commandParameters[i].ParameterName, commandParameters[i].Value);
				cmd.Parameters[i].Direction = commandParameters[i].Direction;
			}
		}
	}

	public override void FillingTables(List<TableInfo>? info)
	{
		string? commandText = "select user_tables.table_name as TABLENAME ,constraint_name as PKNAME\r\n                                                from user_tables \r\n                                                left join user_constraints t on t.constraint_type='P' and t.table_name = user_tables.table_name\r\n                                                order by user_tables.table_name";
		IDataReader dataReader = ExecuteReader(commandText);
		while (dataReader.Read())
		{
			info.Add(new TableInfo
			{
				TableName = dataReader["TABLENAME"].ToString(),
				PrimaryKeyName = dataReader["PKNAME"].ToString()
			});
		}
	}

	public override void FillingColumns(List<ColumnInfo> info)
	{
		string? commandText = $"WITH TEST AS ( \r\n                                                select col.column_name,col.table_name,con.constraint_type  \r\n                                                from user_constraints con,  user_cons_columns col \r\n                                                where con.constraint_name = col.constraint_name \r\n                                                and con.constraint_type in ('P','R') and con.owner='{base.Owner}') \r\n                                                select  user_tab_columns.TABLE_NAME as TABLENAME,user_tab_columns.COLUMN_NAME as COLUMNNAME, DATA_TYPE as TYPENAME,DATA_LENGTH as LENGTH,case NULLABLE when 'N' then 'Not Null' when 'Y' then 'Null' end ISNULL,\r\n                                                case t.constraint_type when 'P' then '1' else '0' end  PrimaryKey,\r\n                                                case t.constraint_type when 'R' then '1' else '0' end  FOREIGNKEY,user_tab_columns.DATA_SCALE,user_tab_columns.column_id as COLUMNINDEX,DATA_DEFAULT AS DEFAULTVALUE\r\n                                                from   user_tab_columns   \r\n                                                left join TEST t on  t.TABLE_NAME = user_tab_columns.TABLE_NAME and t.COLUMN_NAME = user_tab_columns.COLUMN_NAME\r\n                                                ";
		IDataReader dataReader = ExecuteReader(commandText);
		while (dataReader.Read())
		{
			info.Add(new ColumnInfo
			{
				TableName = dataReader["TABLENAME"].ToString(),
				ColumnName = dataReader["COLUMNNAME"].ToString(),
				DefaultValue = dataReader["DEFAULTVALUE"].ToString(),
				TypeName = dataReader["TYPENAME"].ToString(),
				Length = dataReader["LENGTH"].ToString(),
				IsPrimaryKey = (dataReader["PRIMARYKEY"].ToString() != string.Empty && dataReader["PRIMARYKEY"].ToString() != "0"),
				IsNull = (dataReader["ISNULL"].ToString() == "Null"),
				DataScale = dataReader["DATA_SCALE"].ToString(),
				ColumnIndex = Convert.ToInt32(dataReader["COLUMNINDEX"].ToString()),
				IsForeignKey = (dataReader["FOREIGNKEY"].ToString() != string.Empty && dataReader["FOREIGNKEY"].ToString() != "0")
			});
		}
	}

	public override void FillingIndexs(List<IndexInfo>? info)
	{
		string? commandText = $" select  i.index_name as INDNAME,i.table_name as TABNAME,column_name as COLNAMES,descend as Sequence,'' as REMARK,\r\n                                                    case UNIQUENESS WHEN 'NONUNIQUE' THEN 0 WHEN 'UNIQUE' THEN 1 END IsUnique\r\n\u3000\u3000\u3000\u3000                                            from user_indexes i,user_ind_columns c\r\n\u3000\u3000\u3000\u3000\u3000                                           where i.index_name=c.index_name\r\n\u3000\u3000\u3000\u3000\u3000                                           and table_owner = '{base.Owner}'";
		IDataReader dataReader = ExecuteReader(commandText);
		while (dataReader.Read())
		{
			IndexInfo indexInfo = new IndexInfo();
			indexInfo.ColumnName = dataReader["COLNAMES"].ToString();
			indexInfo.Sequence = dataReader["Sequence"].ToString();
			indexInfo.TableName = dataReader["TABNAME"].ToString();
			indexInfo.IndexName = dataReader["INDNAME"].ToString();
			indexInfo.IsUnique = dataReader["IsUnique"].ToString() != string.Empty && dataReader["IsUnique"].ToString() == "1";
			IndexInfo item = indexInfo;
			info.Add(item);
		}
	}

	public override void FillingRelations(List<RelationshipInfo>? info)
	{
		string? commandText = string.Format("SELECT c2.table_name as tabname,c1.constraint_name as refkeyname,col1.column_name as colname, \r\n                                                c2.table_name as reftabname,col2.column_name as refcolname,\r\n                                                case c2.delete_rule when 'CASCADE' then 1 else 0 end CascadingDelete,0 as CascadingUpdate\r\n                                                FROM user_constraints c1\r\n                                                left join user_cons_columns col1 on c1.table_name=col1.table_name and c1.constraint_name=col1.constraint_name\r\n                                                ,user_constraints c2\r\n                                                left join user_cons_columns col2 on c2.table_name=col2.table_name and c2.constraint_name=col2.constraint_name\r\n                                                where c1.constraint_name = c2.r_constraint_name and col1.position = col2.position\r\n                                                and c1.owner='{0}' and c2.owner='{0}'", base.Owner);
		IDataReader dataReader = ExecuteReader(commandText);
		while (dataReader.Read())
		{
			info.Add(new RelationshipInfo
			{
				TableName = dataReader["TABNAME"].ToString(),
				RefKeyName = dataReader["REFKEYNAME"].ToString().Trim(),
				ColumnName = dataReader["COLNAME"].ToString().Trim(),
				RelationTableName = dataReader["REFTABNAME"].ToString().Trim(),
				RelationColumnName = dataReader["REFCOLNAME"].ToString().Trim(),
				IsCascadingDelete = (dataReader["CascadingDelete"].ToString() != string.Empty && dataReader["CascadingDelete"].ToString() == "1"),
				IsCascadingUpdate = (dataReader["CascadingUpdate"].ToString() != string.Empty && dataReader["CascadingUpdate"].ToString() == "1")
			});
		}
	}

	public override void FillingViews(List<ViewInfo> info)
	{
		IDataReader dataReader = ExecuteReader(" SELECT NAME,TEXT as CONTENT,LINE FROM user_source WHERE TYPE = 'VIEW' order by NAME,LINE");
		while (dataReader.Read())
		{
			info.Add(new ViewInfo
			{
				ViewName = dataReader["VIEWNAME"].ToString(),
				Content = dataReader["CONTENT"].ToString()
			});
		}
	}

	public override void FillingProcs(List<ProcInfo> info)
	{
		IDataReader dataReader = ExecuteReader("SELECT NAME as PROCNAME,TEXT as CONTENT,LINE,(select count(0) from user_arguments where user_source.name =user_arguments.object_name) as  PARAMCOUNT FROM user_source WHERE TYPE = 'PROCEDURE' ORDER BY NAME,LINE");
		while (dataReader.Read())
		{
			if (!(dataReader["PARAMCOUNT"].ToString() == "0"))
			{
				string procName = dataReader["PROCNAME"].ToString();
				ProcInfo procInfo = info.FirstOrDefault((ProcInfo o) => o.ProcName == procName);
				if (procInfo == null)
				{
					info.Add(new ProcInfo
					{
						ProcId = dataReader["PROCNAME"].ToString(),
						ProcName = dataReader["PROCNAME"].ToString(),
						Content = dataReader["CONTENT"].ToString()
					});
				}
				else
				{
					procInfo.Content += dataReader["CONTENT"].ToString();
				}
			}
		}
	}

	public override void FillingProcParams(List<ProcParamInfo> info)
	{
		IDataReader dataReader = ExecuteReader("SELECT OBJECT_NAME AS PROCNAME,ARGUMENT_NAME AS PARMNAME,DATA_LENGTH AS LENGTH,DATA_TYPE AS TYPENAME,SEQUENCE AS \"INDEX\",  IN_OUT AS STATUS,DATA_SCALE AS SCALE   FROM user_arguments  ORDER BY OBJECT_NAME,SEQUENCE");
		while (dataReader.Read())
		{
			info.Add(new ProcParamInfo
			{
				ProcId = dataReader["PROCNAME"].ToString(),
				ProcParamName = dataReader["PARMNAME"].ToString(),
				TypeName = dataReader["TYPENAME"].ToString(),
				Length = dataReader["LENGTH"].ToString(),
				State = dataReader["STATUS"].ToString(),
				ParamIndex = Convert.ToInt32(dataReader["INDEX"].ToString()),
				DataScale = dataReader["SCALE"].ToString()
			});
		}
	}

	public override void FillingTypeInfos(List<TypeNameInfos> info)
	{
		DataTable schema = _mOracleConnection.GetSchema("DataTypes");
		if (schema != null && schema.Rows.Count > 0)
		{
			info.AddRange(from DataRow row in schema.Rows
				select new TypeNameInfos
				{
					TypeName = row["TypeName"].ToString()
				});
		}
	}
}
